﻿<?php

   header("Content_Type: text/comma-separated-values");
	//Nom du fichier en sortie
   $fileName = 'export_'.($_POST['clas']!=""?$_POST['clas'].'_':"").date("m.d.y_G\hi").'.csv';
	header('Content-disposition: attachement; filename="'.$fileName.'"; charset=UTF-8');
	header("Content-Type: application/csv-tab-delimited-table");
	
   // la localisation est redéfinie au cas où la configuration du serveur ne serait pas bonne
	setlocale(LC_ALL, "fr_FR");
	
	// les variables php sont encodées en UTF-8
	mb_internal_encoding("UTF-8");
	
	// lors de l'utilisation d'expression régulières, celles-ci seront encodées en UTF-8
	mb_regex_encoding("UTF-8");
   
   // Inclusion fichier de configuration
	require_once("../../config.php");
	
	// Inclusion les fonctions php externes
	require_once("../../librairies/fonctions.php");

   $connexion = connexionBDD($bdd_hostname, $bdd_login, $bdd_password, $bdd_database);
	
	if($connexion === false){
		die("ERREUR: connexion impossible à la base de données");
	}
   
   
/**Construction de la requete multicritères**/

   $entete = array();
   $cptEntete = 0;
   
   
   $query_select = "SELECT DISTINCT t.CODETAXON AS id, a.ABREVIATIONAUTEUR AS auteur,
f.NOMFAMILLE AS famille, sf.NOMSOUSFAMILLE AS sous_famille, g.NOMGENRE AS genre,
 sg.NOMSOUSGENRE AS sous_genre, e.NOMESPECE AS espece,
se.NOMSOUSESPECE AS sous_espece, v.NOMVARIETE AS variete ";
   
	$query_jointures = "FROM taxon t ";
   $query_jointures .= "INNER JOIN classification c ON c.CODECLASSIFICATION = t.CODECLASSIFICATION ";
   $query_jointures .= "INNER JOIN auteur a ON (a.CODEAUTEUR = t.CODEAUTEUR) ";
   $query_jointures .= "INNER JOIN est_de_famille l1 INNER JOIN famille f ON f.CODEFAMILLE = l1.CODEFAMILLE AND l1.CODETAXON = t.CODETAXON AND l1.CODECLASSIFICATION = t.CODECLASSIFICATION ";
   $query_jointures .= "LEFT JOIN est_de_sous_famille l11 INNER JOIN sous_famille sf ON sf.CODESOUSFAMILLE = l11.CODESOUSFAMILLE ON l11.CODETAXON = t.CODETAXON AND l11.CODECLASSIFICATION = t.CODECLASSIFICATION ";
   $query_jointures .= "INNER JOIN appartient_a_genre l2 INNER JOIN genre g ON g.CODEGENRE = l2.CODEGENRE AND l2.CODETAXON = t.CODETAXON AND l2.CODECLASSIFICATION = t.CODECLASSIFICATION ";
   $query_jointures .= "LEFT JOIN appartient_a_sous_genre l22 INNER JOIN sous_genre sg ON sg.CODESOUSGENRE = l22.CODESOUSGENRE ON l22.CODETAXON = t.CODETAXON AND l22.CODECLASSIFICATION = t.CODECLASSIFICATION ";
   $query_jointures .= "INNER JOIN est_de_espece l3 INNER JOIN espece e ON e.CODEESPECE = l3.CODEESPECE AND l3.CODETAXON = t.CODETAXON AND l3.CODECLASSIFICATION = t.CODECLASSIFICATION ";
   $query_jointures .= "LEFT JOIN est_de_sous_espece l33 INNER JOIN sous_espece se ON se.CODESOUSESPECE = l33.CODESOUSESPECE ON l33.CODETAXON = t.CODETAXON AND l33.CODECLASSIFICATION = t.CODECLASSIFICATION ";
   $query_jointures .= "LEFT JOIN a_comme_variete l4 INNER JOIN variete v ON v.CODEVARIETE = l4.CODEVARIETE ON l4.CODETAXON = t.CODETAXON AND l4.CODECLASSIFICATION =
t.CODECLASSIFICATION ";
   
     
   $entete[0] = "idTaxon";
   $entete[1] = "Auteur";
   $entete[2] = "Famille";
   $entete[3] = "Sous-Famille";
   $entete[4] = "Genre";
   $entete[5] = "Sous-Genre";
   $entete[6] = "Espece";
   $entete[7] = "Sous-Espece";
   $entete[8] = "Variete";
   $cptEntete=9;
   
  
   $query_where = " ";
   
   if($_POST['clas'] == ""){
      $query_select .= ", c.NOMCLASSIFICATION AS nomClassif ";
      $entete[$cptEntete] = "Classification";
      $cptEntete++;
   } else {
      $query_where = "WHERE c.NOMCLASSIFICATION = \"".$_POST['clas']."\" ";
   }
   
   if(isset($_POST['air'])) {
      $entete[$cptEntete] = "Aire Geographique";
      $cptEntete++;
   }
   
   if(isset($_POST['nver'])) {
      $entete[$cptEntete] = "Nom Vernaculaire";
      $cptEntete++;
   }
   
   if(isset($_POST['mibi'])) {
      $entete[$cptEntete] = "Milieu Biologique";
      $cptEntete++;
   }
   
   if(isset($_POST['tybi'])) {
      $entete[$cptEntete] = "Type Biologique";
      $cptEntete++;
   }
   
   if(isset($_POST['prop'])){
      $entete[$cptEntete] = "Propriete Espece";
      $cptEntete++;
   }
   
   if(isset($_POST['parc'])){
      $entete[$cptEntete] = "Parcelle";
      $cptEntete++;
   }
   
   if(isset($_POST['nbSG'])){
      $entete[$cptEntete] = "Sachets de graines";
      $cptEntete++;
   }
   
   if(isset($_POST['ephe'])){
      $entete[$cptEntete] = "Etat phenologique";
      $cptEntete++;
   }

   
   $query_get_taxons = $query_select.$query_jointures.$query_where."ORDER BY id LIMIT 0,5000";
   $time_start = microtime(true);
   $result_get_taxons = mysql_query($query_get_taxons, $connexion) or logError("RECUPERATION  TAXONS-".$query_get_taxons."-".mysql_error());
   
   $compte = 0;
   
   while($tab = mysql_fetch_assoc($result_get_taxons)){
      $tab_get_exp[$compte] = $tab;

      $query_idEspece = "SELECT e.CODEESPECE FROM espece e WHERE e.NOMESPECE = ".mysqlString($tab_get_exp[$compte]['espece']);
      $result_idEspece = mysql_query($query_idEspece, $connexion) or logError("RECUPERATION  ID ESPECE-".$query_idEspece."-".mysql_error());
      
      $idEspece = mysql_result($result_idEspece,0);
      
      mysql_free_result($result_idEspece);
      
      if(isset($_POST['air'])){
      
         $query_air = "SELECT air.NOMAIREGEOGRAPHIQUE AS nomAir FROM aire_geographique air INNER JOIN espece e ON e.CODEESPECE =$idEspece AND e.CODEAIREGEOGRAPHIQUE = air.CODEAIREGEOGRAPHIQUE";
      
         $result_air = mysql_query($query_air, $connexion) or logError("RECUPERATION  AIR-".$query_air."-".mysql_error());
         
         $tab_air = false;
         
         while($val = mysql_fetch_assoc($result_air)){
            if($tab_air) {
               $tab_air .= " / ".$val['nomAir'];
            } else {
               $tab_air = $val['nomAir'];
            }
         }
         
         $tab_get_exp[$compte]['nomAir'] = $tab_air;
         
         mysql_free_result($result_air);
      
      }

   
     if(isset($_POST['nver'])){
   		$query_nver = "SELECT nver.NOMVERNACULAIRE as nomNver, air.NOMAIREGEOGRAPHIQUE AS nomAir FROM nom_vernaculaire nver ";
         $query_nver .= "INNER JOIN se_nomme_couramment snc ON snc.CODETAXON = ".$tab_get_exp[$compte]['id']." AND snc.CODENOMVERNACULAIRE = nver.CODENOMVERNACULAIRE ";
         $query_nver .= "INNER JOIN aire_geographique air ON snc.CODEAIREGEOGRAPHIQUE = air.CODEAIREGEOGRAPHIQUE";
      
         $result_nver = mysql_query($query_nver, $connexion) or logError("RECUPERATION  NOM VERNACULAIRE-".$query_nver."-".mysql_error());
         
         $tab_nver = false;
         
         while($val = mysql_fetch_assoc($result_nver)){
            if($tab_nver) {
               $tab_nver .= " / ".$val['nomNver']." (".$val['nomAir'].")";
            } else {
               $tab_nver = $val['nomNver']." (".$val['nomAir'].")";
            }
         }
         
         $tab_get_exp[$compte]['nomNver'] = $tab_nver;
         
         mysql_free_result($result_nver);
   	}
   	
      if(isset($_POST['mibi'])){
      
         $query_mibi = "SELECT mibi.NOMMILIEUBIOLOGIQUE as nomMibi, air.NOMAIREGEOGRAPHIQUE AS nomAir FROM milieu_biologique mibi ";
         $query_mibi .= "INNER JOIN se_trouve_en_milieu_biologique sbm ON sbm.CODETAXON = ".$tab_get_exp[$compte]['id']." AND sbm.CODEMILIEUBIOLOGIQUE = mibi.CODEMILIEUBIOLOGIQUE ";
         $query_mibi .= "INNER JOIN aire_geographique air ON sbm.CODEAIREGEOGRAPHIQUE = air.CODEAIREGEOGRAPHIQUE";
      
         $result_mibi = mysql_query($query_mibi, $connexion) or logError("RECUPERATION  MILIEUX BIOLOGIQUES-".$query_mibi."-".mysql_error());
         
         $tab_mibi = false;
         
         while($val = mysql_fetch_assoc($result_mibi)){
            if($tab_mibi) {
               $tab_mibi .= " / ".$val['nomMibi']." (".$val['nomAir'].")";
            } else {
               $tab_mibi = $val['nomMibi']." (".$val['nomAir'].")";
            }
         }
         
         $tab_get_exp[$compte]['nomMibi'] = $tab_mibi;
         
         mysql_free_result($result_mibi);
      
   	}
       
      if(isset($_POST['tybi'])){

         $query_tybi = "SELECT tybi.NOMTYPEBIOLOGIQUE as nomTybi, air.NOMAIREGEOGRAPHIQUE AS nomAir FROM type_biologique tybi ";
         $query_tybi .= "INNER JOIN est_de_type_biologique etb ON etb.CODETAXON = ".$tab_get_exp[$compte]['id']." AND etb.CODETYPEBIOLOGIQUE = tybi.CODETYPEBIOLOGIQUE ";
         $query_tybi .= "INNER JOIN aire_geographique air ON etb.CODEAIREGEOGRAPHIQUE = air.CODEAIREGEOGRAPHIQUE";
      
         $result_tybi = mysql_query($query_tybi, $connexion) or logError("RECUPERATION TYPES BIOLOGIQUES-".$query_tybi."-".mysql_error());
         
         $tab_tybi = false;
         
         while($val = mysql_fetch_assoc($result_tybi)){
            if($tab_tybi) {
               $tab_tybi .= " / ".$val['nomTybi']." (".$val['nomAir'].")";
            } else {
               $tab_tybi = $val['nomTybi']." (".$val['nomAir'].")";
            }
         }
         
         $tab_get_exp[$compte]['nomTybi'] = $tab_tybi;
         
         mysql_free_result($result_tybi);

   	}
     
      if(isset($_POST['prop'])){
   		$query_prop = "SELECT prop.NOMPROPRIETEESPECE as nomProp, pp.VALEUR as libelle FROM propriete_espece prop ";
         $query_prop .= "INNER JOIN possede_propriete pp ON pp.CODEESPECE = $idEspece AND pp.CODEPROPRIETEESPECE = prop.CODEPROPRIETEESPECE ";
         
         $result_prop = mysql_query($query_prop, $connexion) or logError("RECUPERATION PROPRIETES ESPECE-".$query_prop."-".mysql_error());
         
         $tab_prop = false;
         
         while($val = mysql_fetch_assoc($result_prop)){
            if($tab_prop) {
               $tab_prop .= " / ".$val['nomProp'].($val['libelle']!=NULL?" : ".$val['libelle']:"");
            } else {
               $tab_prop = $val['nomProp'].($val['libelle']!=NULL?" : ".$val['libelle']:"");
            }
         }
         
         $tab_get_exp[$compte]['nomProp'] = $tab_prop;
         
         mysql_free_result($result_prop);
         
      }
       
   	if(isset($_POST['parc'])){
   		$query_parc = "SELECT parc.NOMPARCELLE as nomParc FROM parcelle parc ";
         $query_parc .= "INNER JOIN touffe ON touffe.CODETAXON = ".$tab_get_exp[$compte]['id']." AND touffe.CODEPARCELLE = parc.CODEPARCELLE ";
         
         $result_parc = mysql_query($query_parc, $connexion) or logError("RECUPERATION PARCELLES-".$query_parc."-".mysql_error());
         
         $tab_parc = false;
         
         while($val = mysql_fetch_assoc($result_parc)){
            if($tab_parc) {
               $tab_parc .= " / ".$val['nomParc'];
            } else {
               $tab_parc = $val['nomParc'];
            }
         }
         
         $tab_get_exp[$compte]['nomParc'] = $tab_parc;
         
         mysql_free_result($result_parc);
   	}

      
   	if(isset($_POST['nbSG'])){
   		$query_SG = "SELECT sgrain.QUANTITEGRAINESDISPONIBLES as quoSG, sgrain.REFERENCESACHETGRAINES as ref FROM sachet_graines sgrain ";
         $query_SG .= "WHERE sgrain.CODETAXON = ".$tab_get_exp[$compte]['id'];
         
         $result_SG = mysql_query($query_SG, $connexion) or logError("RECUPERATION SACHETS GRAINES-".$query_SG."-".mysql_error());
         
         $tab_SG = false;
         
         while($val = mysql_fetch_assoc($result_SG)){
            if($tab_SG) {
               $tab_SG .= " / ".$val['ref'].($val['quoSG']!=""?" (".$val['quoSG'].")":"");
            } else {
               $tab_SG = $val['ref'].($val['quoSG']!=""?" (".$val['quoSG'].")":"");
            }
         }
         
         $tab_get_exp[$compte]['nbSG'] = $tab_SG;
         
         mysql_free_result($result_SG);
         
   	}
   	
      
   	//On ne peut pas associer un etat phenologique a une plante dans l'interface
   	if(isset($_POST['ephe'])){
   		$query_ephe = "SELECT ephe.NOMETATPHENOLOGIQUE as nomEphe FROM etat_phenologique ephe ";
         $query_ephe .= "INNER JOIN a_ete_vu_taxon avt ON  avt.CODETAXON = ".$tab_get_exp[$compte]['id']." AND avt.CODEETATPHENOLOGIQUE = ephe.CODEETATPHENOLOGIQUE";
         
         $result_ephe = mysql_query($query_ephe, $connexion) or logError("RECUPERATION ETAT PHENOLOGIQUE-".$query_ephe."-".mysql_error());
         
         $tab_ephe = false;
         
         while($val = mysql_fetch_assoc($result_ephe)){
            if($tab_ephe) {
               $tab_ephe .= " / ".$val['nomEphe'];
            } else {
               $tab_ephe = $val['nomEphe'];
            }
         }
         
         $tab_get_exp[$compte]['nomEphe'] = $tab_ephe;
         
         mysql_free_result($result_ephe);
   	}
      
      $compte++;
   
   }
     
   mysql_free_result($result_get_taxons);
   
   foreach ($entete as $elem) {
      echo "\"$elem\",";
   }
   echo "\n";
   
   for($i=0;$i<$compte;$i++){
      foreach ($tab_get_exp[$i] as $elem) {
         echo "\"$elem\",";
      }
      echo "\n";
   }
   
   $time_end = microtime(true);
   echo "Time : ".($time_end-$time_start)." sec";
   


?>